-- @owner: opentestcase003
-- @date: 2022-07-28
-- @testpoint: dense_rank函数测试，在right join中作为查询列

--step1: 创建表；expect:成功
drop table if exists t1_ustore_function_denserank_0048;
create table t1_ustore_function_denserank_0048(id1 int,id2 int,id3 smallint,id4 int,num1 float,char1 varchar(500)) with (storage_type=ustore);
insert into t1_ustore_function_denserank_0048 values ( 1 , -1 , 2 , 5 , 4041.04329387 , ' a哪吒 ' );
insert into t1_ustore_function_denserank_0048 values ( 3 , 3 , 6 , 3 , 4005.70237548 , ' 010012228 ' );
insert into t1_ustore_function_denserank_0048 values ( 1 , -1 , 3 , 3 , 4030.1242131 , ' 数字字符字母 ' );
insert into t1_ustore_function_denserank_0048 values ( 1 , 4 , 4 , 4 , 4001.87754238 , ' 010012228 ' );
insert into t1_ustore_function_denserank_0048 values ( 3 , 4 , 4 , 0 , 4046.25584633 , ' 数字字符字母 ' );
insert into t1_ustore_function_denserank_0048 values ( 2 , -2 , 6 , 4 , 4003.68558645 , ' 数字字符字母 ' );
insert into t1_ustore_function_denserank_0048 values ( 1 , -2 , 2 , 3 , 4044.9145853 , '' );
insert into t1_ustore_function_denserank_0048 values ( 2 , 4 , 9 , 0 , 4035.26588922 , ' 010012228 ' );
insert into t1_ustore_function_denserank_0048 values ( 3 , -2 , 1 , 2 , 4011.51183805 , ' 010012228 ' );
insert into t1_ustore_function_denserank_0048 values ( 0 , 3 , 9 , 4 , 4004.05504939 , ' ilhfjejgyhg ' );
insert into t1_ustore_function_denserank_0048 values ( 10 , 2 , 1 , 2 , 4020.02593705 , ' ilhfjejgyhg ' );
insert into t1_ustore_function_denserank_0048 values ( null , null , 9 , null , 4004.05504939 , null );
insert into t1_ustore_function_denserank_0048 values ( null , null , null , null , null , ' ilhfjejgyhg ' );
insert into t1_ustore_function_denserank_0048 values ( null , null , null , null , null , null );

drop table if exists t2_ustore_function_denserank_0048;
create table t2_ustore_function_denserank_0048 WITH (STORAGE_TYPE=USTORE) as select * from t1_ustore_function_denserank_0048;
insert into t2_ustore_function_denserank_0048 select * from t2_ustore_function_denserank_0048;
insert into t2_ustore_function_denserank_0048 values ( 1 , -1 , null , 5 , 4041.04329387 , ' A哪吒 ' );
insert into t2_ustore_function_denserank_0048 values ( 3 , null, 6 , 3 , 4005.70237548 , null );
insert into t2_ustore_function_denserank_0048 values ( 4 , null , 3 , 3 , 4030.1242131 , ' 数字字符字母 ' );
insert into t2_ustore_function_denserank_0048 values ( 1 , -244 , 4 , null , null , ' 010012228 ' );
insert into t2_ustore_function_denserank_0048 values ( null , 4 , 4 , 0 , 4046.25584633 , ' 数字字符字母 ' );
insert into t2_ustore_function_denserank_0048 values ( 2 , -2 , null , 4 , null , ' 数字字符字母 ' );
insert into t2_ustore_function_denserank_0048 values ( null , -2 , 2 , 3 , 4044.9145853 , ' 数字字符字母 ' );
insert into t2_ustore_function_denserank_0048(id1) values ( 9);

drop table if exists t4_ustore_function_denserank_0048;
create table t4_ustore_function_denserank_0048(col_1 bigint, col_2 timestamp, col_3 bool,col_4 decimal,col_5 clob ,
col_6 interval day to second ,col_7 nchar(30) ,col_8 blob ,col_9 raw(100),col_10 date,
col_11 bytea ,col_12 bytea,col_13 varchar2(800)) with (storage_type=ustore);
drop sequence if exists s_ustore_function_denserank_0048;
create sequence s_ustore_function_denserank_0048 increment by 1 start with 10000;
insert into t4_ustore_function_denserank_0048 values(s_ustore_function_denserank_0048.nextval,to_date('2019-09-05 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',3.141592665735674554784786,lpad('abc','50','a@123&^%djgk'),(interval '4 5:12:10.222' day to second(3)),rpad('abc','30','uegc%^4'),'101001010','0faadb9',to_date('1981-2-20', 'yyyy-mm-dd'),'101001010','01010111111110000000000',lpad('jhafd韩子高各机构测','80','ajge'));
insert into t4_ustore_function_denserank_0048 values(s_ustore_function_denserank_0048.nextval,to_date('2019-09-06 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',3.141592665735674554784787,lpad('abc','55','a@123&^%djgk'),(interval '4 5:12:10.222' day to second(3)),rpad('ab','30','uegc%^4'),'101010101010','0faadb9',to_date('1981-2-21', 'yyyy-mm-dd'),'1001010101010','01010111111110000000001100000',lpad('jhafd韩子高各机构测','82','ajgedf'));
insert into t4_ustore_function_denserank_0048 values(s_ustore_function_denserank_0048.nextval,to_date('2019-09-07 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',3.141592665735674554784788,lpad('abc','100','a@123&^%djgk'),(interval '4 5:12:10.222' day to second(3)),rpad('abc','29','uegc%^4'),'101010101010','0faadb9',to_date('1981-3-20', 'yyyy-mm-dd'),
'1010100101010','010101111111100000000011100000',lpad('jhafd韩子高各机构测','81','ajge'));
insert into t4_ustore_function_denserank_0048 values(s_ustore_function_denserank_0048.nextval,to_date('2019-09-08 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'false',3.141592665735674554784789,lpad('abc','500','a@123&^%djgk'),(interval '4 5:12:10.222' day to second(3)),rpad('abc','28','uegc%^4'),'1010101001010','0faadb9',to_date('1981-3-21', 'yyyy-mm-dd'),
'10101001010111','010101111111100000000000000',lpad('jhafd韩子高各机构测','185','ajge'));
insert into t4_ustore_function_denserank_0048 values(s_ustore_function_denserank_0048.nextval,to_date('2019-09-05 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',3.1415926657356745547847861,lpad('abc','20','a@123&^%djgk'),(interval '4 5:13:10.222' day to second(3)),rpad('abc','30','uegc%^4'),empty_blob(),'0faadb9',to_date('1981-2-20', 'yyyy-mm-dd'),'10101001010','01010111111110000000000',lpad('jhafd韩子h高各机构测','80','ajge'));

--step2: dense_rank函数测试，在right join中作为查询列；expect:成功
select distinct t1.id1,t1.char1,t2.id1,t2.id2 ,dense_rank() over(partition by t1.char1,t2.id2 order by t2.id1,t1.id1) f1 
from t1_ustore_function_denserank_0048 t1 right join t2_ustore_function_denserank_0048 t2 on t1.char1=t2.char1 order by t1.char1 nulls first,t2.id2 nulls last,t2.id1 nulls first,t1.id1 nulls last limit 10;
select t1.id2,t1.num1,COL_1,COL_2,dense_rank() over(partition by t1.id2,t2.COL_2 order by t2.COL_1,t1.num1,t1.id3 nulls last) f1 from t1_ustore_function_denserank_0048 t1 right join t4_ustore_function_denserank_0048 t2 on (t1.id2+10000)=COL_1 limit 10;
select t1.id3,COL_1,COL_3,COL_4,COL_6,dense_rank() over(partition by t1.id2,COL_6 order by COL_1,t1.id3,COL_4 nulls last,COL_3) f1 from t1_ustore_function_denserank_0048 t1 right join t4_ustore_function_denserank_0048 t2 on (t1.id2+10000)=COL_1 and COL_1 is not null limit 10;

--step3: 清理表；expect:成功
drop sequence s_ustore_function_denserank_0048;
drop table t1_ustore_function_denserank_0048;
drop table t2_ustore_function_denserank_0048;
drop table t4_ustore_function_denserank_0048;
